---
redirect_from:
  - /backend/sql/reference/joins
---

## Views

The best practice to query joins using SQL API is to use views. This is the preferred way of
joining as it provides you control over the joining path for complex use cases.
While BI tools would see the view as a table, in fact, no materialization is done until Cube is queried.
Whenever Cube view is queried through SQL API, Cube tries to maximize member pushdown so only required parts of the view are materialized at query time.
Cube also solves fan and chasm traps based on the dimensions selected in the query, so if measure aggregation types are properly set up, you will see correct results in BI tools even though cubes and views are seen just as tables.

<CodeTabs>

```yaml
views:
  - name: orders_users
    cubes:
      - join_path: orders
        includes:
          - status
          - count

      - join_path: orders.users
        prefix: true
        includes: 
          - id
          - city
          - state
```

```javascript
view(`orders_users`, {
  cubes: [
    {
      join_path: orders,
      includes: ['status', 'count']
    },
    {
      join_path: orders,
      includes: ['id', 'city', 'state']
    },
  ],
});
```

</CodeTabs>

Now, it is possible to get orders count by users city with the following query.

```sql
cube=> SELECT count, city FROM orders_users;
 count |   user_city
-------+---------------
  1416 | Los Angeles
  1412 | Seattle
  1365 | Mountain View
  1263 | New York
  1220 | Austin
  1164 | Chicago
  1101 | San Francisco
  1059 | Palo Alto
(8 rows)
```

# Joins

The SQL API supports joins through `__cubeJoinField` virtual column, which allows end users to control how specific cubes are joined.
This is considered advanced functionality, and views should be used where possible.
Join can also be done through `CROSS JOIN`. Usage
of `__cubeJoinField` in a join instructs Cube to perform join as it's defined in
a data model. Cube generates the correct joining conditions for the underlying
data source.

For example, the following query joins the `orders` and `products` tables under
the hood with `orders.product_id = products.id`, exactly the same way as the
REST API query does:

```sql
cube=> SELECT p.name, SUM(o.count) FROM orders o LEFT JOIN products p ON o.__cubeJoinField = p.__cubeJoinField GROUP BY 1 LIMIT 5;
           name           | SUM(o.count)
--------------------------+--------------
 Tasty Plastic Mouse      |          121
 Intelligent Cotton Ball  |          119
 Ergonomic Steel Tuna     |          116
 Intelligent Rubber Pants |          116
 Generic Wooden Gloves    |          116
(5 rows)
```

Or through `CROSS JOIN`:

```sql
cube=> SELECT p.name, sum(o.count) FROM orders o CROSS JOIN products p GROUP BY 1 LIMIT 5;
           name           | SUM(o.count)
--------------------------+--------------
 Tasty Plastic Mouse      |          121
 Intelligent Cotton Ball  |          119
 Ergonomic Steel Tuna     |          116
 Intelligent Rubber Pants |          116
 Generic Wooden Gloves    |          116
(5 rows)
```

In the resulting query plan, you won't see any joins as you can't see those for
REST API queries either:

```sql
cube=> EXPLAIN SELECT p.name, sum(o.count) FROM orders o LEFT JOIN products p ON o.__cubeJoinField = p.__cubeJoinField GROUP BY 1 LIMIT 5;
   plan_type   |         plan
---------------+-----------------------
 logical_plan  | CubeScan: request={  +
               |   "measures": [      +
               |     "orders.count"   +
               |   ],                 +
               |   "dimensions": [    +
               |     "products.name"  +
               |   ],                 +
               |   "segments": [],    +
               |   "limit": 5         +
               | }
 physical_plan | CubeScanExecutionPlan+
               |
(2 rows)
```

This feature allows you to join cubes even joined transitively only.

In most of the BI tools you'd use `__cubeJoinField` to define joins between cube
tables. In tools that allow defining custom SQL datasets, you can use joined
tables as a dataset SQL. For example:

```sql
SELECT o.count as count, p.name as product_name, p.description as product_description
FROM orders o
CROSS JOIN products p;
```

Please note we use aliasing to avoid name clashing between cube members in a
resulting data set. In this case, wrapped SQL will be properly processed by
Cube, pushing down all operations to Cube query:

```sql
cube=> SELECT product_name, SUM(count) FROM (
  SELECT o.count as count, p.name as product_name, p.description as product_description
  FROM orders o CROSS
  JOIN products p
) joined
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
       product_name       | SUM(joined.count)
--------------------------+-------------------
 Tasty Plastic Mouse      |               121
 Intelligent Cotton Ball  |               119
 Ergonomic Steel Tuna     |               116
 Intelligent Rubber Pants |               116
 Generic Wooden Gloves    |               116
(5 rows)
```

We can see this by introspecting the `EXPLAIN` plan for this query:

```sql
cube=> EXPLAIN SELECT product_name, SUM(count) FROM (
  SELECT o.count as count, p.name as product_name, p.description as product_description
  FROM orders o
  CROSS JOIN products p
) joined
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
   plan_type   |         plan
---------------+-----------------------
 logical_plan  | CubeScan: request={  +
               |   "measures": [      +
               |     "orders.count"   +
               |   ],                 +
               |   "dimensions": [    +
               |     "products.name"  +
               |   ],                 +
               |   "segments": [],    +
               |   "order": [         +
               |     [                +
               |       "orders.count",+
               |       "desc"         +
               |     ]                +
               |   ],                 +
               |   "limit": 5         +
               | }
 physical_plan | CubeScanExecutionPlan+
               |
(2 rows)
```

Please note even if `product_description` is in the inner selection, it isn't
evaluated in the final query as it isn't used in any way.

